Integrating Power BI with Microsoft Excel

Course: Microsoft Power BI Training

Topic: Integrating Power BI with Microsoft Excel

Course Link: Microsoft Power BI Training – JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Integrating Power BI with Microsoft Excel

Microsoft Excel and Microsoft Power BI are powerful business intelligence tools that help organizations collect, organize, analyze, visualize, and share business information. Excel is widely used for spreadsheets, formulas, PivotTables, data entry, and quick calculations, while Power BI is designed for interactive dashboards, data modeling, advanced analytics, and report sharing.

Integrating Excel with Power BI allows users to combine the flexibility of spreadsheets with the analytical and visualization capabilities of Power BI. Users can import Excel workbooks into Power BI, transform spreadsheet data using Power Query, build relationships between tables, create DAX measures, and publish interactive reports. They can also analyze Power BI semantic models from Excel using connected PivotTables and tables.

This integration supports a complete reporting workflow, from preparing data in Excel to building a dashboard in Power BI and conducting additional analysis in Excel.

1.1 What Is Power BI?

Power BI is Microsoft's business intelligence platform for connecting to data sources, preparing data, creating analytical models, developing interactive reports, and sharing insights.

Its major components include:

• Power BI Desktop: Used to connect to data, transform it, create data models, and design reports.
• Power BI Service: The online platform used to publish, share, manage, and collaborate on reports and semantic models.
• Power BI Mobile: Provides access to dashboards and reports on supported mobile devices.
• Power Query: Provides tools to clean, transform, and combine data.
• DAX: Data Analysis Expressions, used to create calculated columns, measures, and other analytical calculations.

1.2 What Is Microsoft Excel?

Microsoft Excel is a spreadsheet application used to store and organize data, perform calculations, create charts, analyze information, and generate reports.

Important Excel features include:

• Worksheets, rows, columns, and structured tables.
• Formulas and functions such as SUM, IF, SUMIFS, and XLOOKUP.
• PivotTables and PivotCharts.
• Power Query for data preparation.
• Power Pivot and the Excel Data Model, where supported.
• Conditional formatting, charts, slicers, and data validation.

1.3 Why Integrate Excel with Power BI?

Many organizations already maintain sales records, budgets, employee details, inventory lists, and financial transactions in Excel. Integrating these workbooks with Power BI allows users to reuse existing data instead of manually rebuilding reports.

• Convert spreadsheet data into interactive dashboards.
• Combine multiple Excel worksheets and workbooks.
• Analyze large collections of business data using a structured data model.
• Reduce repeated manual reporting and copy-paste operations.
• Share consistent metrics across teams.
• Explore Power BI data through familiar Excel PivotTables.
• Refresh connected reports when their source data changes, subject to connection and refresh settings.

2. Learning Objectives

After completing this topic, learners should be able to:

• Explain the relationship between Excel and Power BI.
• Import Excel workbooks into Power BI Desktop.
• Prepare Excel data for reliable reporting.
• Use Power Query to clean and transform imported data.
• Understand the difference between importing data and creating a live connection.
• Build relationships between Excel-derived tables.
• Create measures and interactive reports from spreadsheet data.
• Publish reports and configure appropriate refresh options.
• Connect Excel to a Power BI semantic model using Analyze in Excel.
• Export data from Power BI to Excel for further analysis.
• Troubleshoot common integration and refresh problems.

3. Understanding the Excel and Power BI Integration Workflow

The integration workflow depends on the direction in which data moves. Excel can provide source data to Power BI, and Power BI can provide governed analytical data back to Excel.

3.1 Excel to Power BI

In this workflow, Excel is the source of the data and Power BI is used for advanced analysis and reporting.

1. Prepare the source data in Excel.
2. Convert the data range into a structured Excel table when appropriate.
3. Open Power BI Desktop.
4. Choose the Excel workbook as a data source.
5. Select the required worksheets or tables.
6. Clean and transform the data using Power Query.
7. Load the data into the Power BI model.
8. Create relationships, measures, and report visuals.
9. Publish the report to the Power BI Service if required.
10. Configure refresh settings according to the source location and connection method.

3.2 Power BI to Excel

In this workflow, Power BI contains the analytical model, and Excel is used for additional spreadsheet-based exploration.

1. Publish a semantic model to the Power BI Service.
2. Confirm that the user has the required access and Build permission.
3. Open the semantic model or report in the Power BI Service.
4. Choose Analyze in Excel or another supported Excel connection option.
5. Open the generated workbook.
6. Create PivotTables, PivotCharts, or connected tables.
7. Refresh the workbook connection when updated Power BI data is required.

For official instructions, see Connect Excel to Power BI semantic models.

3.3 Integration Flow Diagram

Excel Workbook
      |
      v
Power BI Desktop
      |
      v
Power Query Transformation
      |
      v
Data Model and Relationships
      |
      v
DAX Measures and Report Visuals
      |
      v
Power BI Service
      |
      +------------------------------+
      |                              |
      v                              v
Interactive Dashboards        Excel Connection
                                     |
                                     v
                            PivotTables and Analysis

4. Preparing Excel Data Before Importing It into Power BI

Data quality is one of the most important factors in successful integration. Poorly structured spreadsheets can cause incorrect data types, missing records, broken transformations, and inaccurate calculations.

4.1 Organize Data in a Tabular Format

A well-structured dataset generally contains one header row followed by records. Each column should represent one attribute, and each row should represent one record.

Example of a properly structured sales dataset:

4.2 Convert the Data Range into an Excel Table

Excel Tables make datasets easier to manage and can help Power BI recognize the data as a named table.

1. Open the Excel workbook.
2. Select any cell within the dataset.
3. Press Ctrl+T or select Insert > Table.
4. Confirm the selected range.
5. Enable the option indicating that the table has headers.
6. Click OK.
7. Open the Table Design tab and assign a meaningful name, such as SalesData.
8. Save the workbook.

Meaningful table names make data sources easier to identify and maintain.

4.3 Apply Data Preparation Best Practices

• Use unique and descriptive column names.
• Remove unnecessary blank rows and columns.
• Keep one data type per column wherever possible.
• Store dates as actual date values instead of inconsistent text.
• Remove duplicate records when duplicates are not valid business transactions.
• Use consistent category names, such as North, South, East, and West.
• Avoid merged cells inside the source dataset.
• Keep totals and decorative headings outside the main tabular dataset.
• Document the meaning and units of important fields.
• Use separate tables for unrelated entities when that structure is appropriate.

5. Importing an Excel Workbook into Power BI Desktop

Importing is one of the most common ways to integrate Excel with Power BI. Power BI reads the selected data into its model so it can be analyzed using relationships, DAX, and report visuals.

5.1 Step-by-Step Import Process

1. Open Microsoft Power BI Desktop.
2. On the Home tab, select Get data.
3. Choose Excel workbook.
4. Browse to the required Excel file.
5. Select Open.
6. Review the Navigator window.
7. Select the required worksheets, named tables, or supported ranges.
8. Choose Transform Data if the data needs cleaning, or Load if it is ready.
9. Wait for Power BI to process the selected data.
10. Review the imported tables in the Data or Model view.

5.2 Understanding the Navigator Window

The Navigator window displays the available data objects within the selected workbook. Depending on the workbook structure, these may include worksheets and named Excel tables.

5.3 Example: Importing Sales Data

Suppose a workbook named SalesReport.xlsx contains a table called SalesData. The table contains order dates, products, regions, quantities, and sales amounts.

1. Choose Get data > Excel workbook.
2. Select SalesReport.xlsx.
3. Choose the SalesData table in Navigator.
4. Select Transform Data.
5. Verify that OrderDate has a Date data type.
6. Verify that Quantity is numeric and SalesAmount is a suitable numeric or currency type.
7. Remove blank records and correct inconsistent values.
8. Select Close & Apply.

The imported table is now available for modeling and report creation.

6. Importing Multiple Excel Worksheets and Workbooks

Business data is often distributed across several worksheets or files. Power BI can combine compatible sources so that users can analyze the information in one reporting environment.

6.1 Importing Multiple Worksheets

A workbook might contain separate worksheets for sales, products, customers, and regions. These worksheets can be imported as separate tables when they represent different entities or types of information.

For example:

• Sales: Transaction-level information.
• Products: Product names, categories, and product identifiers.
• Customers: Customer identifiers, names, and customer segments.
• Regions: Regional codes and descriptions.

Keeping these datasets separate may allow a cleaner model than combining every attribute into one large spreadsheet.

6.2 Combining Similar Excel Files

Suppose monthly sales are stored in separate files such as January.xlsx, February.xlsx, and March.xlsx. If each file has a compatible structure, Power Query can combine the files into a single transaction table.

1. Store the files in a consistent folder or supported location.
2. In Power BI Desktop, select Get data and choose the appropriate folder or file connector.
3. Browse to the folder containing the files.
4. Choose Combine and Transform Data when the option is available.
5. Review the sample file and transformation steps.
6. Confirm that the column names and data types are consistent.
7. Load the combined result.

When a new monthly file is added to the same source folder, a refresh may include it if the query and file selection rules are configured correctly.

6.3 Append Versus Merge

Use append when datasets represent more records of the same type. Use merge when you need to enrich a dataset with related attributes.

7. Using Power Query to Prepare Excel Data

Power Query is the data preparation engine used in both Excel and Power BI. It supports repeatable transformation steps that can be rerun when data is refreshed.

7.1 Opening Power Query Editor

1. Import an Excel workbook into Power BI Desktop.
2. Choose Transform Data.
3. Review the preview of each selected table.
4. Apply the required transformations.
5. Choose Close & Apply to load the transformed results.

7.2 Common Transformation Operations

7.3 Example of Power Query M Code

Power Query transformations can be represented in the M language. The following example illustrates selecting an Excel table and assigning data types.

let
    Source = Excel.Workbook(
        File.Contents("C:\Reports\SalesReport.xlsx"),
        null,
        true
    ),
    SalesTable = Source{[Item="SalesData", Kind="Table"]}[Data],
    ChangedTypes = Table.TransformColumnTypes(
        SalesTable,
        {
            {"OrderDate", type date},
            {"Product", type text},
            {"Region", type text},
            {"Quantity", Int64.Type},
            {"SalesAmount", Currency.Type}
        }
    )
in
    ChangedTypes

This example assumes that the file exists at the specified path and contains a named Excel table called SalesData with the listed columns. The path and column names must be adjusted for the actual workbook.

7.4 Benefits of Power Query

• Reduces repetitive manual data-cleaning work.
• Stores transformation steps for reuse during refresh.
• Supports data from multiple sources.
• Helps standardize data types and column names.
• Provides a repeatable data preparation process.

8. Understanding Import, DirectQuery, and Live Connections

Choosing the correct connection approach is important because it affects data freshness, performance, modeling flexibility, and maintenance.

Important distinction: Importing an Excel workbook into Power BI is not the same as connecting Excel to a published Power BI semantic model. Excel files used as imported sources generally need their data refreshed into the Power BI model when the source changes. A connected Excel workbook can query a Power BI semantic model and refresh its connected PivotTables or tables when permitted.

8.1 When Should You Use Import Mode?

• When the source data is an Excel workbook.
• When report interactivity and model calculations are important.
• When data can be refreshed periodically.
• When the dataset fits the available capacity and model limits.

8.2 When Is a Live Connection Useful?

• When an organization already maintains a trusted Power BI semantic model.
• When users need to analyze centralized measures and dimensions in Excel.
• When consistent definitions of metrics are important across teams.
• When users want to build PivotTables without recreating the underlying data model.

9. Building a Data Model from Excel Tables

A data model organizes related tables so that information can be analyzed together. After importing Excel data, review whether the tables should be connected through relationships.

9.1 Example Data Model

Consider three tables imported from Excel:

• Sales: OrderID, OrderDate, ProductID, CustomerID, Quantity, SalesAmount.
• Products: ProductID, ProductName, Category.
• Customers: CustomerID, CustomerName, Region.

The Sales table stores transactions. The Products and Customers tables provide descriptive information for analyzing those transactions.

9.2 Creating Relationships

1. Open Model view in Power BI Desktop.
2. Locate the Sales, Products, and Customers tables.
3. Create a relationship between Sales[ProductID] and Products[ProductID].
4. Create a relationship between Sales[CustomerID] and Customers[CustomerID].
5. Verify that the dimension-side keys are unique where a one-to-many relationship is intended.
6. Review relationship cardinality and filter direction.
7. Test the relationships using table visuals and measures.

Products[ProductID]  1 ---- *  Sales[ProductID]
Customers[CustomerID] 1 ---- *  Sales[CustomerID]

In this example, one product can appear in many sales records, and one customer can have many orders. ProductID and CustomerID should be consistently typed and correctly populated.

9.3 Why Relationships Matter

• Allow measures to be analyzed by product, customer, or region.
• Reduce unnecessary duplication of descriptive data.
• Improve model organization and maintainability.
• Support more reliable filtering and aggregation.
• Make report development easier as the model grows.

10. Creating DAX Measures from Excel Data

DAX is the formula language used for calculations in Power BI models. Measures are evaluated according to the current filter context, allowing the same calculation to return different results by date, product, region, or other dimensions.

10.1 Total Sales

Total Sales =
SUM(Sales[SalesAmount])

This measure adds the values in the SalesAmount column for the current filter context.

10.2 Total Quantity

Total Quantity =
SUM(Sales[Quantity])

This measure calculates the total quantity sold.

10.3 Number of Orders

Order Count =
DISTINCTCOUNT(Sales[OrderID])

This measure counts unique order identifiers. It is useful when an order can contain multiple transaction rows.

10.4 Average Order Value

Average Order Value =
DIVIDE(
    [Total Sales],
    [Order Count],
    0
)

DIVIDE is useful because it handles division by zero using the specified alternative result.

10.5 Year-to-Date Sales

For time-intelligence calculations, create and configure a proper date table and relate it to the Sales table.

Sales YTD =
TOTALYTD(
    [Total Sales],
    'Date'[Date]
)

This calculation assumes a suitable date table named Date with a Date column and an active relationship to the transaction date. Configure the date table appropriately for the model.

10.6 Measures Versus Excel Formulas

11. Creating Interactive Power BI Reports from Excel Data

Once the Excel data is loaded and modeled, create a report that helps users answer business questions quickly.

11.1 Create a Sales Summary Page

1. Open Report view.
2. Add a Card visual displaying Total Sales.
3. Add another Card for Order Count.
4. Add a bar chart showing Total Sales by Region.
5. Add a line chart showing Total Sales by month.
6. Add a table displaying Product, Quantity, and Total Sales.
7. Add a slicer for Region.
8. Add a date slicer for the reporting period.
9. Format titles, labels, number formats, and visual alignment.
10. Test the report by selecting different slicer values.

11.2 Recommended Report Layout

11.3 Benefits of Power BI Visualizations

• Make important metrics easier to understand.
• Enable interactive exploration without editing spreadsheet formulas.
• Support comparisons across multiple business dimensions.
• Help identify trends, unusual results, and performance gaps.
• Provide a shared view of business performance.

12. Publishing Power BI Reports and Managing Refresh

Publishing makes reports and semantic models available through the Power BI Service, subject to workspace permissions and licensing or capacity requirements.

12.1 Publishing a Report

1. Save the Power BI Desktop file.
2. Select Publish on the Home ribbon.
3. Sign in if prompted.
4. Choose the appropriate workspace.
5. Wait for publishing to complete.
6. Open the report in the Power BI Service.
7. Verify the report, model, and access permissions.

12.2 Refreshing Imported Excel Data

When the original workbook changes, the imported Power BI model does not necessarily update immediately. The model must be refreshed so that it reads the latest source data.

• Power BI Desktop: Use Refresh to reload data from the configured sources.
• Power BI Service: Configure semantic model refresh options where supported.
• Local workbook sources: Confirm that the service can access the file through a supported location and connection setup.
• Cloud workbook sources: Confirm credentials, permissions, source settings, and refresh support.

Refresh options vary with the source, storage mode, connection type, credentials, gateway requirements, and service configuration. Do not assume that saving an Excel workbook automatically updates every Power BI report.

12.3 Refresh Troubleshooting Checklist

• Confirm that the workbook still exists at the configured path.
• Check whether the workbook or its table name has changed.
• Verify source credentials and permissions.
• Review gateway configuration if the source requires one.
• Inspect Power Query steps for renamed or missing columns.
• Check whether the source file is locked or unavailable.
• Confirm the refresh status and read the detailed error message.
• Test the refresh in Power BI Desktop before changing service settings.

13. Connecting Excel to a Power BI Semantic Model with Analyze in Excel

Analyze in Excel lets users connect an Excel workbook to an existing Power BI semantic model. This is useful when a team wants the flexibility of Excel while relying on centrally maintained business measures and model definitions.

13.1 Prerequisites

• The semantic model must be available in the Power BI Service.
• The user must have the required Power BI access and Build permission, or an applicable workspace role.
• The organization must allow the relevant Excel live-connection capability in its tenant settings.
• The user must have an appropriate license and access to the required capacity or workspace.
• Supported Excel and connectivity components must be available.

Access requirements can vary by tenant settings, licensing, capacity, and connection method. Consult your Power BI administrator if the model does not appear.

13.2 Method A: Start from the Power BI Service

1. Open the Power BI Service.
2. Navigate to the workspace containing the semantic model or report.
3. Open the More options menu for the relevant item.
4. Select Analyze in Excel if the option is available.
5. Download or open the generated workbook.
6. If prompted, enable the required content after checking the source and security prompt.
7. Use the PivotTable Fields pane to select model tables, columns, and measures.
8. Build a PivotTable or PivotChart.

13.3 Method B: Start from Excel

1. Open Excel Desktop or Excel for the web.
2. Open the Data tab.
3. Select Get Data and locate the From Power BI option, where available.
4. Search for the required semantic model.
5. Select the model and choose Connect.
6. Insert a PivotTable or connected table, depending on the available options.
7. Arrange the fields and measures to build the analysis.

13.4 Example: Analyzing Regional Sales

Suppose a Power BI semantic model contains a Sales table, a Region field, a Date field, and a Total Sales measure.

1. Connect Excel to the semantic model.
2. Insert a PivotTable.
3. Drag Region into Rows.
4. Drag Total Sales into Values.
5. Add Date to Filters or use a suitable date grouping.
6. Insert a PivotChart if a visual comparison is required.
7. Refresh the PivotTable when updated model data is needed.

This approach allows users to analyze centralized Power BI measures in Excel without rebuilding the complete semantic model in the workbook.

Read the official guide: Connect Excel to Power BI semantic models.

14. Exporting Power BI Data to Excel

Exporting allows users to work with data from a Power BI visual in Excel. Depending on the export option, the result may be a static copy of data or an Excel workbook that supports a live connection to the Power BI semantic model.

14.1 Exporting Data from a Visual

1. Open a Power BI report.
2. Locate the visual containing the required data.
3. Open the visual's More options menu.
4. Select Export data.
5. Choose the available data format and export type.
6. If available and appropriate, select the Excel option with a live connection.
7. Select Export and open the resulting workbook.

Available options depend on the report, visual, model, permissions, administrator settings, and export limitations. Not every visual supports every export option.

14.2 Static Export Versus Live-Connected Export

14.3 Important Export Considerations

• Exported data may reflect the current visual's filters and summarized values.
• Underlying data export may be restricted or unavailable.
• Export limits can apply to row counts and supported file types.
• Column order and formatting may not always match the report visual exactly.
• Confidential information should only be exported when authorized.
• A static export should not be mistaken for a continuously synchronized workbook.

For details, see Create Excel workbooks with refreshable Power BI data.

15. Using Excel PivotTables and PivotCharts with Power BI Data

PivotTables and PivotCharts allow users to summarize and explore data through a familiar spreadsheet interface. When connected to a Power BI semantic model, these tools can use the model's tables and measures.

15.1 Creating a PivotTable

1. Connect Excel to the required Power BI semantic model.
2. Insert a PivotTable using the available connection option.
3. Drag descriptive fields into Rows or Columns.
4. Drag numerical fields or measures into Values.
5. Apply filters to focus on relevant records.
6. Format the resulting report.

15.2 Creating a PivotChart

1. Select a cell within the PivotTable.
2. Choose Insert > PivotChart, if available for the workbook.
3. Select a suitable chart type.
4. Review chart labels, titles, and number formatting.
5. Test the chart using filters or slicers.

15.3 Example Analysis

A regional manager wants to compare monthly revenue for different product categories. The manager can place Month in Rows, Product Category in Columns, and Total Sales in Values. A PivotChart can then display the comparison visually.

Excel provides flexible formatting and ad hoc analysis, while the Power BI semantic model supplies centralized measures and relationships.

16. Data Refresh and Synchronization Between Excel and Power BI

Excel and Power BI do not automatically synchronize every file and report in both directions. The correct refresh method depends on whether Excel is acting as the original data source, a Power BI-connected analysis tool, or a destination for exported data.

16.1 Scenario A: Excel Is the Source

When Power BI imports an Excel workbook, changes made to the workbook must be read by a Power BI refresh before they appear in the imported model.

Excel Workbook Updated
          |
          v
Power BI Refresh
          |
          v
Updated Semantic Model
          |
          v
Updated Report (after successful refresh)

16.2 Scenario B: Power BI Is the Source for Excel

When Excel connects to a Power BI semantic model, refreshing the connected PivotTable or table requests updated data from the model. The model itself must also contain current data for the workbook to reflect the latest source information.

Underlying Data Source
          |
          v
Power BI Model Refresh
          |
          v
Power BI Semantic Model
          |
          v
Excel Connection Refresh
          |
          v
Updated PivotTable or Connected Table

16.3 Scenario C: Data Was Exported as a Snapshot

If the data was exported as a static file, refreshing the worksheet does not automatically reconnect it to the original Power BI visual. Export the data again or use an appropriate connected workbook workflow when recurring refresh is required.

16.4 Refresh Best Practices

• Document the source workbook and its storage location.
• Use stable Excel table names and column names.
• Keep credentials and permissions up to date.
• Configure refresh schedules only when appropriate for the data source.
• Test refreshes after changing the workbook structure.
• Check the Power BI Service refresh history for failures.
• Explain to report consumers whether the workbook is a snapshot or a live-connected file.

17. Security and Access Control

Data integration must preserve appropriate access controls. A user should only view or export information they are authorized to access.

17.1 Power BI Permissions

• Workspace roles determine the level of access to Power BI content.
• Build permission allows eligible users to create new content using a semantic model.
• Row-level security (RLS) restricts which rows a user can see when supported by the model and connection.
• Object-level security (OLS) can restrict access to specified tables or columns when configured and supported.
• Export and sharing capabilities may be limited by report settings or tenant policies.

17.2 Excel Workbook Security

• Store workbooks in approved locations.
• Share only with authorized users.
• Use organization-approved sensitivity labels where applicable.
• Do not embed passwords or confidential credentials in workbook formulas or scripts.
• Review exported files before distributing them externally.
• Understand that static exports may contain data that is no longer governed by a live model connection.

For connected workbooks, Power BI permissions and supported security rules continue to matter. However, access controls on the source model do not automatically protect every independently copied or manually redistributed file. Handle exported data carefully.

18. Common Integration Problems and Solutions

19. Best Practices for Excel and Power BI Integration

19.1 Data Preparation

• Keep source tables structured and consistent.
• Use meaningful column names and table names.
• Remove unnecessary columns early in the transformation process.
• Correct data types before creating measures.
• Separate transactions from descriptive dimension data where appropriate.

19.2 Data Modeling

• Use unique keys for dimension tables.
• Prefer a clear star-schema design for many analytical models.
• Avoid ambiguous relationships.
• Use explicit DAX measures for reusable business metrics.
• Document important calculations and assumptions.

19.3 Report Design

• Show the most important KPIs near the top of the report.
• Use appropriate chart types for the business question.
• Keep colors, labels, and number formats consistent.
• Provide slicers for meaningful filtering.
• Test interactions and totals before publishing.

19.4 Maintenance and Performance

• Keep source file locations stable.
• Avoid unnecessarily importing unused columns and rows.
• Monitor model size and refresh duration.
• Use a connected Excel workflow when centralized model measures are needed in spreadsheets.
• Review refresh history and fix recurring errors.
• Record the owner of each source workbook and report.

20. Practical Project: Build a Sales Dashboard from Excel

20.1 Project Objective

Create a Power BI sales dashboard from an Excel workbook, then analyze the published semantic model from Excel using a PivotTable.

20.2 Required Dataset

Create an Excel table named SalesData with the following columns:

20.3 Project Steps

1. Enter the sample records into Excel.
2. Format the dataset as a table named SalesData.
3. Save the workbook as SalesReport.xlsx.
4. Import the workbook into Power BI Desktop.
5. Use Power Query to verify headers and data types.
6. Load the data into the model.
7. Create the Total Sales, Total Quantity, and Order Count measures.
8. Create KPI cards for the three measures.
9. Create a bar chart for Total Sales by Region.
10. Create a chart for Total Sales over time.
11. Add slicers for Category and Region.
12. Publish the report to the Power BI Service.
13. Verify access permissions and model refresh settings.
14. Use Analyze in Excel to connect Excel to the published semantic model.
15. Create a PivotTable with Region in Rows and Total Sales in Values.
16. Refresh the PivotTable and compare its totals with the Power BI report.

20.4 Expected Results

• The Power BI report displays sales totals and regional comparisons.
• Users can filter the report by category and region.
• The Excel PivotTable uses the published Power BI semantic model.
• Totals can be compared across the two tools when filters and data freshness match.
• The learner understands the difference between importing Excel data and connecting Excel to Power BI.

21. Practical Exercises

Exercise 1: Excel Import

Import an Excel workbook containing employee records into Power BI. Identify the required tables, correct data types, and remove unnecessary columns.

Exercise 2: Combine Monthly Files

Create three monthly sales files with the same column structure. Use Power Query to combine them and verify that the total row count and sales amount are correct.

Exercise 3: Create a Data Model

Create separate Sales and Products tables. Establish a relationship using ProductID and build a report that compares sales by product category.

Exercise 4: Connect Excel to Power BI

Publish a semantic model and connect to it from Excel. Build a PivotTable using a Power BI measure and refresh it after the model is updated.

Exercise 5: Troubleshoot Refresh

Rename a source column in an Excel workbook and observe what happens during refresh. Diagnose the error and update the transformation steps to restore the report.

22. Interview Questions and Answers

Q1. What is the purpose of integrating Power BI with Excel?

Answer: It combines Excel's spreadsheet and ad hoc analysis capabilities with Power BI's data modeling, interactive visualization, and report-sharing features.

Q2. How do you import Excel data into Power BI Desktop?

Answer: Select Home > Get data > Excel workbook, select the file, choose the required tables or worksheets in Navigator, and select Load or Transform Data.

Q3. What is Power Query?

Answer: Power Query is a data preparation tool used to connect to sources, clean data, reshape tables, combine datasets, and save repeatable transformation steps.

Q4. Why should an Excel range be converted into a table?

Answer: A structured table provides defined headers and a named range of records. It is easier to select, maintain, and use as a data source than an unstructured worksheet range.

Q5. What is the difference between append and merge?

Answer: Append adds rows from compatible tables, while merge joins tables using matching columns or keys.

Q6. What is Analyze in Excel?

Answer: Analyze in Excel creates an Excel workbook connected to a Power BI semantic model, allowing users to analyze the model with PivotTables and related Excel functionality.

Q7. Does changing an Excel workbook automatically update Power BI?

Answer: Not necessarily. For imported Excel data, the Power BI model usually needs a successful refresh to retrieve the updated source data.

Q8. What is the difference between a static export and a live-connected Excel workbook?

Answer: A static export is a snapshot of exported data. A live-connected workbook can retrieve updated data from the Power BI semantic model when the connection and refresh are supported.

Q9. Why might Analyze in Excel not be available?

Answer: The user may lack Build permission, the required tenant setting may be disabled, licensing may be insufficient, or the model configuration may not support the operation.

Q10. What is a Power BI measure?

Answer: A measure is a DAX calculation evaluated dynamically according to the filter context of the report visual or Excel-connected analysis.

Q11. How can multiple Excel files be combined in Power BI?

Answer: Use a suitable folder or file connector and Power Query's combine or append functionality, provided the files have compatible structures and access is configured correctly.

Q12. What should you check if Power BI totals differ from Excel totals?

Answer: Compare filters, aggregation methods, data freshness, duplicate records, date ranges, data types, and the definitions of the calculations.

Q13. Can Excel use Power BI row-level security?

Answer: Supported live-connected Excel workflows can enforce row-level security from the semantic model. Security behavior depends on the connection method and model configuration.

Q14. What is the difference between Import and DirectQuery?

Answer: Import loads data into the Power BI model and updates it through refresh. DirectQuery sends queries to a supported underlying source rather than importing the entire dataset into the model.

Q15. How do you keep an Excel-connected PivotTable current?

Answer: Ensure that the underlying Power BI model is up to date, then refresh the PivotTable or use the workbook's supported refresh options.

23. Quick Revision Checklist

• Understand how Excel and Power BI complement each other.
• Prepare clean, structured Excel tables.
• Import workbooks using Get Data.
• Use Power Query to clean and combine data.
• Understand Import, DirectQuery, and live connections.
• Create relationships between related tables.
• Build reusable DAX measures.
• Design interactive Power BI reports.
• Publish reports and verify access permissions.
• Understand source refresh versus connected workbook refresh.
• Use Analyze in Excel to explore Power BI semantic models.
• Distinguish static exports from live-connected workbooks.
• Diagnose source, data-type, permission, and refresh errors.
• Apply data security and report maintenance best practices.

24. Official Learning Resources

• Connect Excel to Power BI semantic models – Learn how to connect Excel to a Power BI semantic model.
• Create Excel workbooks with refreshable Power BI data – Learn about Analyze in Excel and connected exports.
• Power BI data in Excel – Review supported connection workflows and considerations.
• Import and analyze data in Excel – Explore Excel data-import and Power Query concepts.
• Business intelligence capabilities in Excel – Review spreadsheet-based analysis tools.

25. Conclusion

Integrating Power BI with Microsoft Excel creates a flexible reporting environment in which spreadsheets can serve as data sources and Excel can also act as an analysis client for published Power BI semantic models. Power Query helps prepare and combine data, the Power BI model organizes relationships and measures, and interactive reports communicate important business insights.

To use this integration effectively, prepare structured Excel data, select the right connection method, build reliable data models, define reusable measures, and understand how refresh works for each workflow. Pay close attention to permissions, export restrictions, and data security when sharing workbooks.

By practicing the import, transformation, modeling, publishing, Analyze in Excel, and refresh processes covered in these notes, learners can build practical skills for spreadsheet-based business intelligence and enterprise reporting.

Explore the course: Microsoft Power BI Training at JustAcademy

Register for a demo: Book a Course Demo

Visit JustAcademy: https://www.justacademy.co/